package com.backend_pay.dao;

import com.backend_pay.entity.IncomeDetail;
import com.backend_pay.entity.PayoutDetail;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Timestamp;
import java.util.List;

@Repository //存储层bean注释，数据流层DAO
public interface PayoutDetailDao extends JpaRepository<PayoutDetail,Integer> {
    /*
    * @Query(value="",nativequery=true)
    * public void findById(int id);*/

    //查询所有支出 findAllById
    @Query(value="select * from payout_detail where userid=?",nativeQuery=true)
    public List<PayoutDetail> findAllById(int userId);

    //添加一条incomeDetail
    @Modifying
    @Transactional
    @Query(value = "insert into payout_detail(userid, type_payout, money, comment,createtime) values (?1,?2,?3,?4,?5);",nativeQuery = true)
    public void addPayoutDetail(Integer userId, String typePayout, Double money, String comment, Timestamp createtime);

    //查询所有收入类型
    @Query(value="select distinct type_payout from payout_detail ",nativeQuery=true)
    public String[] findAllKindOfPayout();
    //按条件查询收入记录
    @Query(value = "select * from payout_detail where userid=?1 and type_payout=?2  and createtime>?3 and createtime<?4",nativeQuery=true)
    public List<PayoutDetail> queryPayoutByCondition(Integer userid, String typePayout, Timestamp start, Timestamp end);
    @Query(value = "select * from payout_detail where userid=?1 and createtime>?2 and createtime<?3",nativeQuery=true)
    public List<PayoutDetail> queryPayoutByConditionNoType(Integer userid,Timestamp start,Timestamp end);

    //按条件查询支出的金额、支出类型
    @Query(value="select sum(cast(money as decimal(12,2))),type_payout from payout_detail where userid=?1 and createtime>?2 and createtime<?3 group by type_payout",nativeQuery=true)
    public List<Object> queryPayoutByDate(Integer userid, Timestamp start, Timestamp end);

    //按条件查询支出的金额、时间
    @Query(value="select sum(cast(money as decimal(12,2))) as money,date_format(createtime,'%Y-%m-%d') as createtime from payout_detail where userid=?1 and createtime>?2 and createtime<?3 group by date_format(createtime,'%Y-%m-%d') order by date_format(createtime,'%Y-%m-%d') desc",nativeQuery=true)
    public List<Object> queryPayoutGroupByDate(Integer userid, Timestamp start, Timestamp end);

    //删除某一用户的所有支出信息
    @Modifying
    @Transactional
    @Query(value="delete from payout_detail where userid=?1",nativeQuery=true)
    public void deletePayoutDetailById(Integer userid);
}
